﻿using com.nm.db.Helper;
using NHibernateVertica;
using System;
using System.Configuration;
using System.Data;
using Vertica.Data.VerticaClient;

namespace DistributeAPI.Helper
{
    public class ErpOrderHelper
    {
        #region  自定义业务处理_cyl

        private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["Vertica7"].ConnectionString;
        private static SQLErpDBHelper SQLDbHelper { get; set; }


        //作者：曹亚亮
        //时间：2017-06-13
        /// <summary>
        /// 根据业务逻辑自定义执行多条SQL语句，实现多数据库事务。
        /// </summary>
        /// <param name="dt">Vertica7数据库的：DataTable</param>
        /// <param name="strTableName">Vertica7数据库的：表名</param>
        /// <param name="delSql">Sql Server数据库的：删除SQL语句</param>
        /// <param name="SQLStringList">Sql Server数据库的：批量执行SQL语句</param>
        /// <param name="procedureSql">Sql Server数据库的：存储过程SQL</param>
        /// <param name="updSql">Vertica7数据库的：修改语句</param>
        /// <returns></returns>
        public static ErpOrderMessage ExecuteSqlTran_Task(DataTable dt, string strTableName, string delSql, string insertSql, string procedureSql, string updSql)
        {

            using (var sqlconn = new VerticaConnection(ConnectionString))
            {
                sqlconn.Open();
                using (var trans = sqlconn.BeginTransaction())
                {
                    try
                    {

                        var vdh = new VerticaDataHelper();
                        var eom = new ErpOrderMessage();
                        var count = 0;
                        vdh.BulkCopy(sqlconn, dt, strTableName);//数据保存到Vertica7
                        new SQLErpDBHelper().ExecuteSql_Task(delSql);//先执行以下数据删除
                        count = SQLDbHelper.ExecuteSql_Task(insertSql);//批量导入Sql Server数据库
                        if (count > 0)
                        {
                            var ds = SQLDbHelper.Query_Task(procedureSql); //RetVal:（0 成功 1和-1 失败）
                            if (ds != null && ds.Tables[0].Rows.Count > 0)
                            {
                                int RetVal = Convert.ToInt32(ds.Tables[0].Rows[0]["RetVal"].ToString());
                                if (RetVal == 0)
                                {
                                    eom.RetVal = RetVal;
                                    eom.BillNo = ds.Tables[1];
                                    trans.Commit();
                                    var cmdUpd = vdh.GetSqlStringCommand(updSql);
                                    vdh.ExecuteNonQuery(cmdUpd); //更改虚拟库存表数据标识
                                }
                                else
                                {
                                    eom.RetVal = RetVal;
                                    eom.BillNo = null;
                                    SQLDbHelper.ExecuteSql_Task(delSql);//删除导入的数据
                                    trans.Rollback();
                                }
                            }
                            else
                            {
                                eom.RetVal = -1;
                                eom.BillNo = null;
                                new SQLErpDBHelper().ExecuteSql_Task(delSql);//删除导入的数据
                                trans.Rollback();
                            }
                        }
                        else
                        {
                            eom.RetVal = -1;
                            eom.BillNo = null;
                            trans.Rollback();
                        }

                        return eom;
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw (ex);
                    }
                }
            }
        }

        //作者：曹亚亮
        //时间：2017-06-14
        /// <summary>
        /// 根据业务逻辑自定义执行多条SQL语句，实现多数据库事务。
        /// </summary>
        /// <param name="delSql">Sql Server数据库的：删除SQL语句</param>
        /// <param name="SQLStringList">Sql Server数据库的：批量执行SQL语句</param>
        /// <param name="procedureSql">Sql Server数据库的：存储过程SQL</param>
        /// <param name="updSql">Vertica7数据库的：修改语句</param>
        /// <returns></returns>
        public static ErpOrderMessage GetErpShopOrder_Task(string delSql, string insertSql, string procedureSql, string updSql)
        {
            try
            {
                var vdh = new VerticaDataHelper();
                var eom = new ErpOrderMessage();
                var count = 0;
                SQLDbHelper.ExecuteSql_Task(delSql);//先执行以下数据删除
                count = SQLDbHelper.ExecuteSql_Task(insertSql);//批量导入Sql Server数据库
                if (count > 0)
                {
                    var ds = SQLDbHelper.Query_Task(procedureSql); //RetVal:（0 4 成功 1和-1 -2 失败）
                    if (ds != null && ds.Tables[0].Rows.Count > 0)
                    {
                        int RetVal = Convert.ToInt32(ds.Tables[0].Rows[0]["RetVal"].ToString());
                        if (RetVal == 0 || RetVal == 4)
                        {
                            eom.RetVal = RetVal;
                            eom.BillNo = ds.Tables[1];
                            var cmdUpd = vdh.GetSqlStringCommand(updSql);
                            vdh.ExecuteNonQuery(cmdUpd); //更改店铺需求表数据标识
                        }
                        else if (RetVal == -2)
                        {
                            eom.RetVal = RetVal;
                            eom.BillNo = ds.Tables[1];
                            SQLDbHelper.ExecuteSql_Task(delSql);//删除导入的数据
                        }
                        else
                        {
                            eom.RetVal = RetVal;
                            eom.BillNo = null;
                            SQLDbHelper.ExecuteSql_Task(delSql);//删除导入的数据
                        }
                    }
                    else
                    {
                        eom.RetVal = -1;
                        eom.BillNo = null;
                        SQLDbHelper.ExecuteSql_Task(delSql);//删除导入的数据
                    }
                }
                else
                {
                    eom.RetVal = -1;
                    eom.BillNo = null;
                }

                return eom;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        public class ErpOrderMessage
        {
            public int RetVal { get; set; }
            public DataTable BillNo { get; set; }
        }


        #endregion
    }
}
